package com.ywjh.inter.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import com.ywjh.inter.entity.EquBindEntity;

public class EquBindDAO {

	public int insert(Connection conn, EquBindEntity entity) throws SQLException {
		int id =-1;
		PreparedStatement ps=conn.prepareStatement("insert into equ_bind(user_id,sn,type,add_time,status,brand,name) values(?,?,?,?,?,?,?)",Statement.RETURN_GENERATED_KEYS);
		ps.setInt(1, entity.getUser_id());
		ps.setString(2, entity.getSn());
		ps.setInt(3, entity.getType());
		ps.setTimestamp(4, entity.getAdd_time());
		ps.setInt(5, entity.getStatus());
		ps.setString(6, entity.getBrand());
		ps.setString(7, entity.getName());
		if (ps.executeUpdate() > 0) {
			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				id = rs.getInt(1);
			}
		}
		return id;
	}

	public boolean update(Connection conn, EquBindEntity entity) throws SQLException {
		boolean b=false;
		PreparedStatement ps=conn.prepareStatement("update equ_bind set user_id=?,sn=?,type=?,add_time=?,status=?,brand=? where id = ?");
		ps.setInt(1, entity.getUser_id());
		ps.setString(2, entity.getSn());
		ps.setInt(3, entity.getType());
		ps.setTimestamp(4, entity.getAdd_time());
		ps.setInt(5, entity.getStatus());
		ps.setString(6, entity.getBrand());
		ps.setInt(7, entity.getId());
		if (ps.executeUpdate() > 0) {
			b=true;
		}
		return b;
	}

	public List<EquBindEntity> list(Connection conn, String sn, int type) throws SQLException {
		List<EquBindEntity> list=new LinkedList<EquBindEntity>();
		PreparedStatement ps=conn.prepareStatement("select * from equ_bind where sn=? and type=? and status=1");
		ps.setString(1, sn);
		ps.setInt(2,type);
		ResultSet rs=ps.executeQuery();
		while(rs.next()) {
			EquBindEntity e=fill(rs);
			list.add(e);
		}
		return list;
	}

	private EquBindEntity fill(ResultSet rs) throws SQLException {
		EquBindEntity e=new EquBindEntity();
		e.setAdd_time(rs.getTimestamp("add_time"));
		e.setId(rs.getInt("id"));
		e.setSn(rs.getString("sn"));
		e.setStatus(rs.getInt("status"));
		e.setType(rs.getInt("type"));
		e.setUser_id(rs.getInt("user_id"));
		e.setBrand(rs.getString("brand"));
		e.setName(rs.getString("name"));
		return e;
	}

	public EquBindEntity find(Connection conn, int user_id, String sn, int type) throws SQLException {
		EquBindEntity entity=null;
		PreparedStatement ps=conn.prepareStatement("select * from equ_bind where user_id=? and sn=? and type=? and status=1");
		ps.setInt(1, user_id);
		ps.setString(2, sn);
		ps.setInt(3,type);
		ResultSet rs=ps.executeQuery();
		if(rs.next()) {
			entity=fill(rs);
		}
		return entity;
	}

	public List<EquBindEntity> listByUserId(Connection conn, int user_id) throws SQLException {
		List<EquBindEntity> list=new LinkedList<EquBindEntity>();
		PreparedStatement ps=conn.prepareStatement("select * from equ_bind where user_id=? and status=1");
		ps.setInt(1,user_id);
		ResultSet rs=ps.executeQuery();
		while(rs.next()) {
			EquBindEntity e=fill(rs);
			list.add(e);
		}
		return list;
	}
	
	public List<EquBindEntity> listByUserIdAndType(Connection conn, int user_id, int type) throws SQLException {
		List<EquBindEntity> list=new LinkedList<EquBindEntity>();
		PreparedStatement ps=conn.prepareStatement("select * from equ_bind where user_id=? and type=? and status=1");
		ps.setInt(1, user_id);
		ps.setInt(2, type);
		ResultSet rs=ps.executeQuery();
		while(rs.next()) {
			EquBindEntity e=fill(rs);
			list.add(e);
		}
		return list;
	}

	public List<EquBindEntity> listByTime(Connection conn, String startTime,String endTime, String users) throws SQLException {
		List<EquBindEntity> list=new LinkedList<EquBindEntity>();
		if(users==null||"".equals(users)) {
			return list;
		}
		PreparedStatement ps=conn.prepareStatement("select * from equ_bind where user_id in ('"+users+"') and date_format(add_time,'%y%m%d') >= date_format(?,'%y%m%d') and date_format(add_time,'%y%m%d') <= date_format(?,'%y%m%d') and status=1");
		ps.setString(1, startTime);
		ps.setString(2, endTime);
		ResultSet rs=ps.executeQuery();
		while(rs.next()) {
			EquBindEntity e=fill(rs);
			list.add(e);
		}
		return list;
	}

}
